package common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 实现数据库操作
 * @author maojj
 *
 */
public class DB
{	
	/**
	 * 连接SQLServer
	 * @param driver - 驱动，例如
	 * 					SqlServer："com.microsoft.sqlserver.jdbc.SQLServerDriver"；
	 * 					MySQL："com.mysql.jdbc.Driver"
	 * @param url - 连接地址，例如：
	 * 					SqlServer："jdbc:sqlserver://172.3.207.27:1433;DatabaseName=bmy"
	 * 					MySQL："jdbc:mysql://172.3.207.13:3306/zentao"
	 * @param user - 用户名
	 * @param password - 密码
	 * @return 连接会话
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static Connection connect(String driver, String url, String user, String password) throws SQLException, ClassNotFoundException
	{
		Class.forName(driver);
		Connection connection = DriverManager.getConnection(url, user, password);
		return connection;
	}
	
	/**
	 * 查询数据库
	 * @param connection - 数据库连接会话
	 * @param sql - SQL语句，只支持select
	 * @param columnLabels - 查询结果需要返回的字段
	 * @return 查询结果
	 * @throws SQLException
	 */
	public static String[][] query(Connection connection, String sql, String[] columnLabels) throws SQLException
	{
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		ResultSet resultSet = preparedStatement.executeQuery();
		
		int columnLabelNumber = columnLabels.length;
		List<String[]> lines = new ArrayList<>();
		while (resultSet.next())		//判断是否还有下一个数据
        {
			String[] line = new String[columnLabelNumber];
			for (int i=0; i<columnLabelNumber; i++)
			{
				line[i] = resultSet.getString(columnLabels[i]);
			}
			lines.add(line);
        }
		
		//关闭数据库连接
		resultSet.close();
		preparedStatement.close();
		connection.close();
		
		String[][] ret = new String[lines.size()][];
		ret = lines.toArray(ret);
		return ret;
	}
	
	/**
	 * 修改数据库
	 * @param connection - 数据库连接会话
	 * @param sql - SQL语句，支持insert和update
	 * @throws SQLException
	 */
	public static void update(Connection connection, String sql) throws SQLException
	{
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.executeUpdate();
		
		//关闭数据库连接
		preparedStatement.close();
		connection.close();	
	}
}

